#!/usr/bin/env python

__author__ = "Roney Reis"
__license__ = "Public"
__version__ = "0.1"
__email__ = "roney.reis@lia.ufc.br"
__status__ = "Development"

import MySQLdb
import logging
import re
from datetime import date
import sys
from lxml import etree


# Path of Properties
pathProperties = "C:/Users/Roney/Documents/eclipseDesenvolvimento/ProjectTCC/src/ufc/tcc.properties"

def insertElementDB_XML(name, value, id_page, conn):
    logging.info("** Start - Insert Element in DB_XMl")
    query = "INSERT INTO elementXML(name, value, id_page) VALUES ('%s','%s', %d)" %(name,value,id_page)

    try:
        cursor = conn.cursor()
        logging.info("** Query: INSERT INTO elementXML(name, value, id_page)")
        cursor.execute(query)
        conn.commit()
    except:
        logging.exception("** Can't execute query %s" %query)
    logging.info("** Finish - Insert Element in DB_XMl")
   
   
def insertDB(query,conn):
    logging.info("** Insert query in DB")
    #logging.info(" ** Query: %s" %query)
    try:
        cursor = conn.cursor()
        cursor.execute(query)
        conn.commit()
        #logging.info("Result: %s" %query)
    except:
        logging.exception("** Can't execute query %s" %query)

def selectOneDB(query,conn):
    logging.info("** Select query in DB")
    logging.info(" ** Query: %s" %query)
    try:
        cursor = conn.cursor()
        cursor.execute(query)
        t = cursor.fetchone()
        logging.info("** Result: %s" %t)
        return t
    except:
        logging.exception("** Can't execute query %s" %query)
        return ""
      
        
def selectText(id_page, conn):
    logging.info("Start selectText - STEP 1")
    # SELECIONA O TEXTO NO BD
    query = "select textHTML from page where id=%d" %id_page
    logging.info("STEP 1 - Query: " + query)
    cursor = conn.cursor()
    cursor.execute(query)
    t = cursor.fetchone()
    if t!=None:
        textHTML = t[0]
        logging.info("STEP 1 - Finish selectText")
        return textHTML
    else:
        logging.info("STEP 1 - There aren't the id %d" %id_page)
        return ''  
       

def preProcessing(text, id_page, conn):
    # Find and save the text title and delete the head's text html 
    logging.info("Start preProcessing - STEP 2")
    logging.info("STEP 2 - Find text title")
    stop = True 
    search = "<title> </head>"
    dSearch = search.split()
    
    count = text.find(dSearch[0])
    while(stop):
        count = count + 1
        if text[count]=='>':
            indexTitle = count + 1
        if text[count]=='<':
            title = text[indexTitle:count]
            stop = False
        if count >100000:
            logging.info("STEP 2 - There aren't the title of text")
            title = " "
            stop = False 
    logging.info("STEP 2 - Text title: " + title)
    insertElementDB_XML("title", title, id_page,conn)
    logging.info("STEP 2 - Finish preProcessing")
    return text[text.find(dSearch[1]):]
    #print text[indexA:(indexA+7)]
    

def trasformation(text, id_page, conn):
    #Find, save and delete parts of text
    logging.info("Start transformation - STEP 3")
    search = "<h1"
    primer = text.find(search)
    text = text[primer:]
    for i in range(1,4):
            logging.info("STEP 3 - Find tag h%d" %i)
            search = "<h%d"%i+" </h%d"%i+">"
            dSearch = search.split()
            
            x = countWords(text, dSearch[0]) #Count the number of tag H{1,2,3 or 4}
            logging.info("STEP 3 - %d tags h%d" %(x,i))
            for i in range(x):
                
                count = text.find(dSearch[0]) 
                TAG = "zero"
                if count >=0: 
                    try:
                        while(text[count]!='>'):
                            text = text.replace(text[count],"",1)
                    except IndexError:
                        print "FIM"
                    text = text.replace(text[count]," ",1)
                    count2 = text.find(dSearch[1])
                    TAG = text[(count+1):count2]
                    text = text.replace(text[count:count2],"",1)
                    
                    count = text.find(dSearch[1])
                    while(text[count]!='>'):
                        text = text.replace(text[count],"",1)
                    text = text.replace(text[count]," ",1)
                    #print "TAG: " +TAG
                
                    i = count
                    #print "TEXT: " + text
                    
                    while(text[i:i+2]!="<h"):
                        i = i+1
                        if i > len(text):
                            break
                    textH2 =  text[count:i]
                    text = text.replace(textH2,"",1)        
                    #print "H2: "+textH2
                    TAG = cleanText(TAG)
                    TAG = " ".join(TAG.split())
                    if len(TAG)<100 and len(TAG)>0:
                        logging.info("STEP 3 - Title:\n %s" %TAG)
                        query = "INSERT INTO trasformation(tag,text,id_page) VALUES('%s','%s',%d)"%(TAG,textH2,id_page)
                        insertDB(query, conn)
                    else:
                        logging.info("STEP 3 - Text is very big")
                    
    logging.info("STEP 3 - Finish transformation")  
                    


def dataMining(id_page, conn):
    #Find a pattern in the table transformation and save in a table
    logging.info("Start data Mining - STEP 4")
    search = "<table </table>"
    dSearch = search.split()
    par1 = "Monthly Uptime"
    par2 = "Monthly Availability"
    logging.info("STEP 4 - Search for %s and %s" %(par1, par2))
    parms = []
    tables = []
    parms.append(par1)
    parms.append(par2)
    # SELECIONA O TEXTO NO BD
    query = "select id,text from trasformation where id_page=%d" %id_page
    logging.info("STEP 4 - Query: %s" %(query))
    cursor = conn.cursor()
    cursor.execute(query)
    rows = cursor.fetchall()
    logging.info("STEP 4 - Number of tuples: %d" %len(rows))
    for row in rows:
        #print row[0]
        text = row[1]
        start = text.find(dSearch[0])
        #print start
        text = text[start:]
        if(start>=0):
            finish = text.find(dSearch[1])
            #print "FINISH: %d" %finish
            #print text
            for i in range(len(parms)):
                string = parms.pop()
                #print "STRING: %s" %string
                count = text.find(string)
                if (count >=0 and count<=finish):
                    tables.append(row[0])
    logging.info("STEP 4 - Tables: %s" %tables)
    logging.info("STEP 4 - Finish data Mining") 
    return tables    

def interpretation():
    pass

def knowledge(text, id_page, conn):
    #Find and save elements of table
    logging.info("Start KnowLedge - STEP 5")
    f = open('C:\\wamp\\www\\tcc\\out_py.xml','w')
    root = etree.Element("sla")
    query = "select value from elementxml where id_page=%d" %id_page
    logging.info("STEP ** - Query: %s" %query)
    title = selectOneDB(query,conn)
    root.set("name",str(title[0]))
    search = "<table <tr <th </tr> </th> </table> <td </td>"
    dSearch = search.split()
    count = text.find(dSearch[0])
    text = text[count+8:]
    
    #print text
    count = text.find(dSearch[1])
    start = text.find(dSearch[2])
    #print "START 1: %d" %start
    if start <0:
        start = text.find(dSearch[6])
    #print "START 2: %d" %start
    finish = text.find(dSearch[4])
    #print "FINISH 1: %d" %finish
    if finish <0:
        finish = text.find(dSearch[7])
    #print "FINISH 2: %d" %finish
    finish = finish + 5
    col1 = text[start:finish]
    col1 = cleanText(col1).strip()
    text = text[finish:]
    print "COL: " + col1 
        
    start = text.find(dSearch[2])
    if start <0:
        start = text.find(dSearch[6])
    finish = text.find(dSearch[4])
    if finish <0:
        finish = text.find(dSearch[7])
    finish = finish + 5 
    col2 = text[start:finish]
    col2 = cleanText(col2).strip()
    text = text[finish:]
    print " " + col2 + "\n"
    
    count = text.find(dSearch[1])
    child2 = etree.SubElement(root,"resource")
    child2.set("name",col1)
    child2.set("value",col2)
    query = "INSERT INTO subelementxml(name,value,id_page) VALUES('%s','%s',%d)"%(col1,col2,id_page)
    logging.info("STEP 5 - Query: %s" %query)
    insertDB(query, conn)
    #print "ORIGEM: " + text
    while(count>=0):
        start = text.find(dSearch[6])
        text = text[start:]
        #print "PRE-COL: " + text
        b = text.find(">")
        text = text[b+1:] 
        finish = text.find(dSearch[7])
        col1 = text[:finish]
        col1 = replaceText(col1)
        col1 = clearTrash(col1).strip()
        print col1
        start = text.find(dSearch[6])
        text = text[start:]
        b = text.find(">")
        text = text[b+1:] 
        finish = text.find(dSearch[7])
        col2 = text[:finish]
        col2 = clearTrash(col2).strip()
        col2 = replaceText(col2)
        text = text[finish:]
        child3 = etree.SubElement(child2,"element")
        child3.set("name",col1)
        child3.set("value",col2)
        print " " + col2 + "\n"
        count = text.find(dSearch[1])
        query = "INSERT INTO subelementxml(name,value,id_page) VALUES('%s','%s',%d)"%(col1,col2,id_page)
        logging.info("STEP 5 - Query: %s" %query)
        insertDB(query, conn)
    print(etree.tostring(root, pretty_print=True))
    f.write(etree.tostring(root, pretty_print=True))
    logging.info("STEP 5 - Finish KnowLedge")
 

def configureLog(logLevel, logName):
    logFile = logName
    logFormat = '[%(asctime)s] %(levelname)s: %(message)s'
    dateFormat= '%d-%m-%Y %H:%M:%S'
    effectiveLevel = getattr(logging, logLevel.upper())
    logging.basicConfig(filename=logFile, filemode='w', level=effectiveLevel, format=logFormat, datefmt = dateFormat)

def cleanText(text):
    logging.info("--Clear html text")
    search = "< >"
    dSearch = search.split()
    count = text.find(dSearch[0])  
    while count >=0:
        try:
            while(text[count]!='>'):
                text = kill_char(text,count)
                #text = text.replace(text[count],"",1)
        except IndexError:
            print "FIM"
        text = text.replace(text[count]," ",1)
        count = text.find(dSearch[0])
    text = clearTrash(text)
    logging.info("--Finish clear html text")
    return text

def kill_char(string, n): # n = position of which character you want to remove 
    begin = string[:n]    # from beginning to n (n not included)
    end = string[n+1:]    # n+1 through end of string
    return begin + end

def clearTrash(text):
    search = "&nbsp; &#"
    dSearch = search.split()
    count = text.find(dSearch[0])
    while(count>=0):
        text = text.replace(dSearch[0],"")
        count = text.find(dSearch[0])
    count = text.find(dSearch[1])
    while(count>=0):
        p = text[count:].find(";") + 1
        aux = text[count:count+p]
        text = text.replace(aux,"")
        count = text.find(dSearch[1])
    return text.strip()

def replaceText(text):
    logging.info(" ---Start Replace Text---")
    search = "&lt; &gt;"
    dSearch = search.split()
    dSearch.append("less than")
    dSearch.append("Less than")
    dSearch.append("greater than")
    dSearch.append("equal to")
    dSearch.append("= or >")
    dSearch.append("= or <")
    count = text.find(dSearch[0])
    while(count>=0):
        text = text.replace(dSearch[0],"<")
        count = text.find(dSearch[0])
    count = text.find(dSearch[1])
    while(count>=0):
        text = text.replace(dSearch[1],">")
        count = text.find(dSearch[1])
    count = text.find(dSearch[2])
    while(count>=0):
        text = text.replace(dSearch[2],"<")
        count = text.find(dSearch[2])  
    count = text.find(dSearch[3])
    while(count>=0):
        text = text.replace(dSearch[3],"<")
        count = text.find(dSearch[3])
    count = text.find(dSearch[4])
    while(count>=0):
        text = text.replace(dSearch[4],">")
        count = text.find(dSearch[4])
    count = text.find(dSearch[5])
    while(count>=0):
        text = text.replace(dSearch[5],"=")
        count = text.find(dSearch[5])
    count = text.find(dSearch[6])
    while(count>=0):
        text = text.replace(dSearch[6],">=")
        count = text.find(dSearch[6])
    count = text.find(dSearch[7])
    while(count>=0):
        text = text.replace(dSearch[7],"<=")
        count = text.find(dSearch[7])
    logging.info(" ---Finish Replace Text---")
    return text
    
    
 
def countWords(text,word):    
    number = text.find(word) 
    count = 0 
    while(number>=0):
        text = text.replace(text[number:number+3],"",1)
        number = text.find(word)
        count = count +1
    return count
   
def execute(url):
    configureLog("DEBUG", "iExtractor-%s.log" % date.today().isoformat())
    logging.info("Started")
    args = {}
    properties = open(pathProperties)
    logging.info("Open Properties")
    parament = []
    for line in properties:
        i = line.find("=") + 1
        aux = line[i:].strip()
        parament.append(aux)
    args["u"] = parament[0] # database user
    args["p"] = parament[1] # database password
    args["d"] = parament[2] # database name
    args["l"] = parament[3] # database host
    logging.info("Arguments: %s" %args)  
    #Open db connection
    try:
        conn = MySQLdb.connect(host=args["l"], user=args["u"], passwd=args["p"], db=args["d"], use_unicode=True, charset='utf8')
    except:
        logging.exception("Can't connect to database")
        conn.close()
    
    query = "select id from page where url='%s'" %url
    id_page = selectOneDB(query,conn) 
    id_page=int(id_page[0])
    if(id_page!=None):
          textHTML = selectText(id_page, conn)
         
          if(len(textHTML)>0):
              textHTML = preProcessing(textHTML, id_page, conn)
              trasformation(textHTML, id_page, conn)
              tables = dataMining(id_page, conn)
              for tab in tables:
                  tab = int(tab)
                  query = "select text from trasformation where id=%d" %tab
                  logging.info("STEP ** - Query: %s" %query)
                  text = selectOneDB(query,conn)
                  text = str(text[0]).replace(u'\xa0', u' ')
                  knowledge(text, id_page, conn)
    conn.close()    
    logging.info("Finished")
    print "--Finish--"
 
 
if __name__ == '__main__':
    print "Start Extractor"
    #url = sys.argv[1]
    execute("https://developers.google.com/storage/sla")
    print "Finish Extractor"
    